﻿using BusinessLogic;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace QlpmClient.Utils
{
    class TreatmentFunction
    {
        ultils ul = new ultils();
        //set datatabe from database
        public DataTable DanhSachKhamBenh(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("patientid", typeof(int));
            dt.Columns.Add("treatmentid", typeof(int));
            dt.Columns.Add("fullname", typeof(String));
            dt.Columns.Add("idcard", typeof(String));
            dt.Columns.Add("sex", typeof(String));
            dt.Columns.Add("dateofbirth", typeof(String));
            dt.Columns.Add("address", typeof(String));
            dt.Columns.Add("phonenumber", typeof(String));
            dt.Columns.Add("relative", typeof(String));
            DataRow dr;
            string sex = "";
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["patientid"] = row[0];
                    dr["treatmentid"] = row[1];
                    dr["fullname"] = row[2];
                    dr["idcard"] = row[3];
                    if (Convert.ToInt32(row[4]) == 1)
                        sex = "Nam";
                    else
                        if (Convert.ToInt32(row[4]) == 2)
                            sex = "Nữ";
                        else
                            sex = "Chưa cập nhật";
                    dr["sex"] = sex;
                    dr["dateofbirth"] = row[5];
                    dr["address"] = row[6];
                    dr["phonenumber"] = row[7];
                    dr["relative"] = row[8];
                    dt.Rows.Add(dr);
                }

            }

            return dt;
        }
        //get danh sách bệnh nhân đang khám, đã khám hoặc chưa khám..
        public DataTable getPatientListToday(MyQueryBL myQueryBL, int treatmentstatusid)
        {
            DataTable dt = new DataTable();
            DateTime time = DateTime.Now;
            string day = ul.Get_Day(time);
            string month = ul.Get_Month(time);
            string year = ul.Get_Year(time);

            string query = "select patient.patientid, treatmentid, fullname, idcard, sex, dateofbirth, address, phonenumber, relative " +
                "from patient inner join treatment on patient.patientid = treatment.patientid "+
                "where day(dateoftreatment) = "+day+" and month(dateoftreatment) = "+month+" and year(dateoftreatment) ="+year+" and treatmentstatusid = "+treatmentstatusid+"";
            string[] str = myQueryBL.doQuery(query);
            dt = DanhSachKhamBenh(str);
            return dt;
        }
        //Tìm kiếm bệnh nhân
        public DataTable getPatientListBySearch(MyQueryBL myQueryBL, string hoTen, string namSinh, int gioiTinh, string soCMND, string ngayKham)
        {
            DataTable dt = new DataTable();

            string sqlHoTen = "";
            if (hoTen != "")
            {
                sqlHoTen = " and fullname like '%"+hoTen+"%' ";
            }
            string sqlNamSinh = "";
            if (namSinh != "")
            {
                sqlNamSinh = " and year(dateofbirth) = " + namSinh;
            }
            string sqlGioiTinh = "";
            if (gioiTinh != 0)
            {
                if (gioiTinh == 1)
                {
                    sqlGioiTinh = " and sex = 1";
                }
                else if (gioiTinh == 2)
                {
                    sqlGioiTinh = " and sex = 2";
                }
            }
            string sqlSoCMND = "";
            if (soCMND != "")
            {
                sqlSoCMND = " and idcard like '%" + soCMND + "%'";
            }
            string sqlNgayKham = "";
            if (ngayKham != "")
            {
                string day = ul.SplitDay(ngayKham);
                string month = ul.SplitMonth(ngayKham);
                string year = ul.SplitYear(ngayKham);

                sqlNgayKham = " and day(dateoftreatment) = " + day + " and month(dateoftreatment) = " + month + " and year(dateoftreatment) =" + year + "";
            }
            string query = "select patient.patientid, treatmentid, fullname, idcard, sex, dateofbirth, address, phonenumber, relative " +
                "from patient inner join treatment on patient.patientid = treatment.patientid " +
                "where 1=1 "+ sqlHoTen + sqlNamSinh + sqlGioiTinh + sqlSoCMND + sqlNgayKham;
            string[] str = myQueryBL.doQuery(query);
            dt = DanhSachKhamBenh(str);
            return dt;
        }
        //Cập nhật khám bệnh cho bệnh nhân
        public void updateTreatment(MyQueryBL myQueryBL, string trieuChung, string chuanDoan, string loiDan, string ngayHen, int ngayThuoc) 
        {
            string query = "";
            myQueryBL.doNonQuery(query);
        }
        // chèn các thuốc được dùng của bệnh nhân
        public void insertTreatmentDetail()
        {

        }
        public DataTable DataTableSymptom(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("symptomid", typeof(int));
            dt.Columns.Add("symptomname", typeof(String));
            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["symptomid"] = row[0];
                    dr["symptomname"] = row[1];
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }
        //get all triệu chứng
        public DataTable getTrieuChung(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("symptomname", typeof(String));
            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["symptomname"] = row[0];
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }
        //get all chuẩn đoán
        public DataTable getChuanDoan(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("diagnosisid", typeof(int));
            dt.Columns.Add("diagnosisname", typeof(String));
            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["diagnosisid"] = row[0];
                    dr["diagnosisname"] = row[1];
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }
        //get all lời dặn
        public DataTable getLoiDan(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("advicename", typeof(String));
            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["advicename"] = row[0];
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }
        //get toa thuốc mẫu
        public DataTable getToaThuocMau(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("prescriptionid", typeof(String));
            dt.Columns["prescriptionid"].Caption = "Mã số";
            dt.Columns.Add("prescriptionname", typeof(String));
            dt.Columns["prescriptionname"].Caption = "Toa mẫu";
            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["prescriptionid"] = row[0];
                    dr["prescriptionname"] = row[1];
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }

        //get thuốc
        public DataTable getThuoc(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("medicineid", typeof(String));
            dt.Columns["medicineid"].Caption = "Mã số";
            dt.Columns.Add("medicinename", typeof(String));
            dt.Columns["medicinename"].Caption = "Thuốc";
            dt.Columns.Add("medicinestrength", typeof(String));
            dt.Columns["medicinestrength"].Caption = "Hàm lượng";
            dt.Columns.Add("medicineunitname", typeof(String));
            dt.Columns["medicineunitname"].Caption = "Đơn vị";
            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["medicineid"] = row[0];
                    dr["medicinename"] = row[1];
                    dr["medicinestrength"] = row[2];
                    dr["medicineunitname"] = row[3];
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }
        //get Cách dùng
        public DataTable getCachDung(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("usageid", typeof(String));
            dt.Columns.Add("usagename", typeof(String));
            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["usageid"] = row[0];
                    dr["usagename"] = row[1];
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }
        public int getMaxId(string[] str)
        {
            int max = 0;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    max = Convert.ToInt32(row[0]);
                }
            }
            return max;
        }

        //get lịch sử khám bệnh
        public DataTable getTreatmentHistory(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("treatmentid", typeof(String));
            dt.Columns.Add("dateoftreatment", typeof(String));
            dt.Columns.Add("diagnosisname", typeof(String));
            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["treatmentid"] = row[0];
                    dr["dateoftreatment"] = row[1];
                    dr["diagnosisname"] = row[2];
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }

        //get danh sách thanh toán
        public DataTable getPatientListPayment(MyQueryBL myQueryBL, int maSo, string hoTen, string namSinh, int gioiTinh, string soCMND, string ngayKham, int tinhTrang)
        {
            DataTable dt = new DataTable();

            string sqlMaSo = "";
            if (maSo != 0)
            {
                sqlMaSo = " and treatmentid =" + maSo;
            }
            string sqlHoTen = "";
            if (hoTen != "")
            {
                sqlHoTen = " and fullname like '%" + hoTen + "%' ";
            }
            string sqlNamSinh = "";
            if (namSinh != "")
            {
                sqlNamSinh = " and year(dateofbirth) = " + namSinh;
            }
            string sqlGioiTinh = "";
            if (gioiTinh != 0)
            {
                if (gioiTinh == 1)
                {
                    sqlGioiTinh = " and sex = 1";
                }
                else if (gioiTinh == 2)
                {
                    sqlGioiTinh = " and sex = 2";
                }
            }
            string sqlSoCMND = "";
            if (soCMND != "")
            {
                sqlSoCMND = " and idcard like '%" + soCMND + "%'";
            }
            string sqlNgayKham = "";
            
            if(ngayKham != "")
            {
                string day = "";
                string month = "";
                string year = "";
                if (ngayKham == "today")
                {
                    DateTime time = DateTime.Now;
                    day = ul.Get_Day(time);
                    month = ul.Get_Month(time);
                    year = ul.Get_Year(time);
                }
                else
                {
                    day = ul.SplitDay(ngayKham);
                    month = ul.SplitMonth(ngayKham);
                    year = ul.SplitYear(ngayKham);
                }
               
                sqlNgayKham = " and day(dateoftreatment) = " + day + " and month(dateoftreatment) = " + month + " and year(dateoftreatment) =" + year + "";

            }

            string sqlTinhTrang = "";
            if (tinhTrang != 0)
            {
                sqlTinhTrang = "and paystatus =" + (tinhTrang - 1);
            }
            string query = "select patient.patientid, treatmentid, fullname, idcard, sex, dateofbirth, address, phonenumber, relative, paystatus " +
                "from patient inner join treatment on patient.patientid = treatment.patientid " +
                "where treatmentstatusid = 3 " + sqlMaSo + sqlHoTen + sqlNamSinh + sqlGioiTinh + sqlSoCMND + sqlNgayKham + sqlTinhTrang;
            string[] str = myQueryBL.doQuery(query);
            dt = DanhSachThanhToan(str);
            return dt;
        }
        public DataTable DanhSachThanhToan(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("patientid", typeof(int));
            dt.Columns.Add("treatmentid", typeof(int));
            dt.Columns.Add("fullname", typeof(String));
            dt.Columns.Add("idcard", typeof(String));
            dt.Columns.Add("sex", typeof(String));
            dt.Columns.Add("dateofbirth", typeof(String));
            dt.Columns.Add("address", typeof(String));
            dt.Columns.Add("phonenumber", typeof(String));
            dt.Columns.Add("relative", typeof(String));
            dt.Columns.Add("paystatus", typeof(String));
            dt.Columns.Add("paystatusid", typeof(int));
            DataRow dr;
            string sex = "";
            string status = "";
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["patientid"] = row[0];
                    dr["treatmentid"] = row[1];
                    dr["fullname"] = row[2];
                    dr["idcard"] = row[3];
                    if (Convert.ToInt32(row[4]) == 1)
                        sex = "Nam";
                    else
                        if (Convert.ToInt32(row[4]) == 2)
                            sex = "Nữ";
                        else
                            sex = "Chưa cập nhật";
                    dr["sex"] = sex;
                    dr["dateofbirth"] = row[5];
                    dr["address"] = row[6];
                    dr["phonenumber"] = row[7];
                    dr["relative"] = row[8];
                    if (Convert.ToInt32(row[9]) == 0)
                    {
                        status = "Chưa thanh toán";
                        dr["paystatus"] = status;
                        dr["paystatusid"] = 0;
                    }
                    else
                    {
                        status = "Đã thanh toán";
                        dr["paystatus"] = status;
                        dr["paystatusid"] = 1;
                    }
                   
                    
                    dt.Rows.Add(dr);
                }

            }

            return dt;
        }
        

        //Danh sách thuốc thanh toán của bệnh nhân
        public DataTable getListMedicineByTreatmentId(MyQueryBL myQueryBL, int treatmentid)
        {
            DataTable dt = new DataTable();
            string query = "select TREATMENTDETAIL.medicineid, medicinename, medicinestrength, TREATMENTDETAIL.quantitytotal from (TREATMENTDETAIL inner join MEDICINE on TREATMENTDETAIL.medicineid = MEDICINE.medicineid) where treatmentid =" + treatmentid;
            string[] str = myQueryBL.doQuery(query);
            dt = DanhSachThuocThanhToan(str);
            return dt;
        }
        public DataTable DanhSachThuocThanhToan(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("medicineid", typeof(int));
            dt.Columns.Add("medicinename", typeof(String));
            dt.Columns.Add("medicinestrength", typeof(String));
            dt.Columns.Add("quantitytotal", typeof(int));
            dt.Columns.Add("cost", typeof(int));
            dt.Columns.Add("money", typeof(int));

            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["medicineid"] = row[0];
                    dr["medicinename"] = row[1];
                    dr["medicinestrength"] = row[2];
                    dr["quantitytotal"] = row[3];
                    dr["cost"] = 0;
                    dr["money"] = 0;
                    dt.Rows.Add(dr);
                }

            }

            return dt;
        }

        //load toa mẫu hồ sơ bệnh án
        public DataTable getPrescriptionFormById(MyQueryBL myQueryBL, int prescriptionid)
        {
            DataTable dt = new DataTable();
            string query = "select PRESCRIPTIONFORMDETAIL.medicineid, medicinename, medicinemorning, medicinenoon, medicineafternoon, medicineevening, PRESCRIPTIONFORMDETAIL.quantitytotal, dosage from ((PRESCRIPTIONFORMDETAIL inner join PRESCRIPTIONFORM on PRESCRIPTIONFORMDETAIL.prescriptionid = PRESCRIPTIONFORM.prescriptionid) inner join MEDICINE on PRESCRIPTIONFORMDETAIL.medicineid = MEDICINE.medicineid) where PRESCRIPTIONFORM.prescriptionid=" + prescriptionid;
            string[] str = myQueryBL.doQuery(query);
            dt = DanhSachToaMauTheoId(str);
            return dt;
        }
        public DataTable DanhSachToaMauTheoId(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("maThuoc", typeof(int));
            dt.Columns.Add("tenThuoc", typeof(String));
            dt.Columns.Add("sang", typeof(int));
            dt.Columns.Add("trua", typeof(int));
            dt.Columns.Add("chieu", typeof(int));
            dt.Columns.Add("toi", typeof(int));
            dt.Columns.Add("soLuong", typeof(int));
            dt.Columns.Add("cachDung", typeof(String));

            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["maThuoc"] = Convert.ToInt32(row[0]);
                    dr["tenThuoc"] = row[1];
                    dr["sang"] = Convert.ToInt32(row[2]);
                    dr["trua"] = Convert.ToInt32(row[3]);
                    dr["chieu"] = Convert.ToInt32(row[4]);
                    dr["toi"] = Convert.ToInt32(row[5]);
                    dr["soLuong"] = Convert.ToInt32(row[6]);
                    dr["cachDung"] = row[7];
                    dt.Rows.Add(dr);
                }

            }
            return dt;
        }

        //get Nhà sản xuất by id
        public DataTable getNhaSanXuatById(MyQueryBL myQueryBL, int id)
        {
            DataTable dt = new DataTable();
            string query = "select manufacturerid, manufacturername from manufacturer where manufacturerid =" + id;
            string[] str = myQueryBL.doQuery(query);
            dt = DanhSachNhaSanXuatById(str);
            return dt;
        }
        public DataTable DanhSachNhaSanXuatById(string[] str)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("maNhaSanXuat", typeof(int));
            dt.Columns.Add("nhaSanXuat", typeof(String));

            DataRow dr;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    dr = dt.NewRow();
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    dr["maNhaSanXuat"] = Convert.ToInt32(row[0]);
                    dr["nhaSanXuat"] = row[1];

                    dt.Rows.Add(dr);
                }

            }
            return dt;
        }
        public int DonGiaThuocThanhToan(string[] str)
        {
            int donGia = 0;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    donGia = Convert.ToInt32(row[1]);
                }

            }
            return donGia;

        }
        //get Tien Kham
        public int TienKham(string[] str)
        {
            int tienKham = 0;
            if (str != null)
            {
                for (int i = 0; i < str.Length; i++)
                {
                    string[] row = str[i].Split(new string[] { "<<_>>" }, StringSplitOptions.None);
                    tienKham = Convert.ToInt32(row[0]);
                }

            }
            return tienKham;
        }
        

    }
}
